﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class SqlDbHelper
    {
        public  string strCon = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
        public DataTable executeSqlDataTable(string strCMD)
        {
            SqlDataAdapter sda = new SqlDataAdapter(strCMD, strCon);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            return ds.Tables[0];
        }

        public DataTable executeSqlDataTable(string sql,params SqlParameter[] paras)
        {
            using (SqlConnection con = new SqlConnection(strCon))
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.Parameters.AddRange(paras);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    cmd.Parameters.Clear();
                    con.Close();
                    return dt;
                }
       
            }
        }
        public int executeSqlNonQuery(string strCMD)
        {
            int x = 0;
            using (SqlConnection con = new SqlConnection(strCon))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(strCMD, con);
                x = cmd.ExecuteNonQuery();
            }
            return x;
        }

        public int executeSqlNonQuery(string sql, params SqlParameter[] paras)
        {
            int x = 0;
            using (SqlConnection con = new SqlConnection(strCon))
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.Parameters.AddRange(paras);
                    x = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
            }
            return x;
        }

        public  int  Insert<T>(T t, string tableName) where T : class
        {
            if (t == null || string.IsNullOrEmpty(tableName))
            {
                return 0;
            }
            string columns = GetColmons(t);
            if (string.IsNullOrEmpty(columns))
            {
                return 0;
            }
            string values = GetValues(t);
            if (string.IsNullOrEmpty(values))
            {
                return 0;
            }
            StringBuilder sql = new StringBuilder();
            sql.Append("Insert into " + tableName);
            sql.Append("(" + columns + ")");
            sql.Append(" values(" + values + ")");
            int x = 0;
            using (SqlConnection con = new SqlConnection(strCon))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sql.ToString(), con);
                x = cmd.ExecuteNonQuery();
            }
            return x;
        }

   /// <summary>
   /// 更新实体
   /// </summary>
   /// <typeparam name="T">目标实体类型</typeparam>
   /// <param name="entity">目标实体</param>
   /// <param name="tableName">表名</param>
   /// <param name="pkName">主键名称</param>
   /// <param name="excludeColumns">更新排除列</param>
   /// <returns></returns>

        public  int Update<T>(T entity, string tableName,string pkName,List<string> excludeColumns=null) where T : class
        {
            if (entity == null || string.IsNullOrEmpty(tableName))
            {
                return 0;
            }
            if (string.IsNullOrEmpty(pkName))
            {
                return 0;
            }

            object pkValue = string.Empty;

            StringBuilder sb = new StringBuilder();
            sb.Append("update ");
            sb.Append(tableName);
            sb.Append(" set ");
            Type type = entity.GetType();
            PropertyInfo[] props = type.GetProperties();
            List<object> paraList = new List<object>();
            foreach (var prop in props)
            {
                if (excludeColumns != null && excludeColumns.Count > 0)
                {
                    if (excludeColumns.Contains(prop.Name)) continue;
                }
                if (prop.Name == (string)pkName)
                {
                    pkValue = prop.GetValue(entity);
                }
                else
                {
                    paraList.Add(GetUpdatePara(prop, entity));
                }
            }

            if (paraList.Count == 0)
            {
                return 0;
            }

            sb.Append(string.Join(",", paraList));

            if (pkValue==null)
            {
                throw new Exception("主键不能为空");
            }

            sb.Append(" where ");
            sb.Append(pkName);
            sb.Append(" = ");
            sb.AppendFormat("'{0}'", pkValue);
            int x = 0;
            using (SqlConnection con = new SqlConnection(strCon))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sb.ToString(), con);
                x = cmd.ExecuteNonQuery();
            }
            return x;
        }

        /// <summary>
        /// 根据每页显示数与总记录数计算出总页数
        /// </summary>
        /// <param name="rows">每页显示数</param>
        /// <param name="totalRecord">结果总记录数</param>
        /// <param name="isPagination">是否分页 如果分页则进行计算 不分页则返回null</param>
        /// <returns></returns>
        public int? CalculateTotalPage(int? rows, int? totalRecord, bool isPagination = true)
        {
            if (isPagination && rows > 0)
            {
                return Convert.ToInt32(Math.Ceiling((double)totalRecord / (double)rows));
            }
            else
            {
                return null;
            }
        }

        #region 查询首行首列\/// <summary>
        /// 返回单值的查询方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public  object GetScalar(string sql)
        {
            using (SqlConnection con = new SqlConnection(strCon))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return obj;
            }
        }

        /// <summary>
        /// 返回单值的查询方法（有参数的查询语句）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public  object GetScalar(string sql, params SqlParameter[] para)
        {

            using (SqlConnection con = new SqlConnection(strCon))
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.Parameters.AddRange(para);
                    object obj =  cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return obj;
                }
            }
        }
        #endregion


        #region 获取分页数据
        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetPager(QueryCondition condition)
        {
            DataTable dt = new DataTable();
            string orderStr = string.Empty;
            try
            {
                using (var connection = new SqlConnection(strCon))
                {
                    //排序
                    if (!string.IsNullOrEmpty(condition.sidx))
                    {
                        orderStr = condition.sidx + " " + condition.sort;
                    }
                    int totalCount = 0;
                    DynamicParameters param = new DynamicParameters();
                    param.Add("@pageIndex", condition.pageIndex, DbType.Int32);
                    param.Add("@pageSize", condition.pageSize, DbType.Int32);
                    param.Add("@pkid", condition.pkId, DbType.String);
                    param.Add("@fieldName", condition.fieldNames, DbType.String);
                    param.Add("@tableName", condition.tableName, DbType.String);
                    param.Add("@whereStr", condition.strWhere, DbType.String);
                    param.Add("@orderStr", orderStr, DbType.String);
                    param.Add("@recordTotal", null, DbType.Int32, ParameterDirection.Output);
                    var Reader = connection.ExecuteReader("Pro_GetPager", param: param, commandType: CommandType.StoredProcedure);
                    dt.Load(Reader);
                    totalCount = param.Get<object>("@recordTotal") == null ? 0 : param.Get<Int32>("@recordTotal");
                    condition.total = CalculateTotalPage(condition.pageSize, totalCount);
                    condition.records = totalCount;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return dt;
        }

        #endregion

        /// <summary>      
        /// /// DataTable转换成实体列表     
        /// /// </summary>      
        /// /// <typeparam name="T">实体 T </typeparam>
        /// /// <param name="table">datatable</param>  
        /// /// <returns></returns>     
        public  IList<T> DataTableToList<T>(DataTable table) where T : class
        {
            if (table.Rows == null || table.Rows.Count < 1)
                return new List<T>();
            IList<T> list = new List<T>();
            T model = default(T);
            foreach (DataRow dr in table.Rows)
            {
                model = Activator.CreateInstance<T>();
                foreach (DataColumn dc in dr.Table.Columns)
                {
                    object drValue = dr[dc.ColumnName];
                    PropertyInfo pi = model.GetType().GetProperty(dc.ColumnName);
                    if (pi != null && pi.CanWrite && (drValue != null && !Convert.IsDBNull(drValue)))
                    {
                        pi.SetValue(model, drValue, null);
                    }
                }
                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// 获得类型的列名
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static string GetColmons<T>(T obj)
        {
            if (obj == null)
            {
                return string.Empty;
            }
            return string.Join(",", obj.GetType().GetProperties().Select(p => p.Name).ToList());
        }

        /// <summary>
        /// 获得值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static string GetValues<T>(T obj)
        {
            if (obj == null)
            {
                return string.Empty;
            }
            return string.Join(",", obj.GetType().GetProperties().Select(p => string.Format("'{0}'", p.GetValue(obj))).ToArray());
        }
        /// <summary>
        /// 获得修改参数
        /// </summary>
        /// <param name="property"></param>
        /// <returns></returns>
        private static string GetUpdatePara<T>(PropertyInfo property, T entity)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" {0}='{1}' ", property.Name, property.GetValue(entity));
            return sb.ToString();
        }
    }


    /// <summary>
    /// 列表页面进行查询的参数
    /// </summary>
    public class QueryCondition
    {

        public int? records { get; set; } //总记录数

        public int? total { get; set; } //总页数

        /// <summary>
        /// 每页记录数
        /// </summary>
        public int? pageSize
        {
            get;
            set;

        }

        ///// <summary>
        ///// 当前页数
        ///// </summary>
        public int? pageIndex
        {
            get;
            set;
        }

        /// <summary>
        /// 表名称
        /// </summary>
        public string tableName { get; set; }
        /// <summary>
        /// 字段名称
        /// </summary>
        public string fieldNames { get; set; }
        ///// <summary>
        ///// 主键id
        ///// </summary>
        public string pkId
        {
            get;
            set;
        }
        /// <summary>
        /// 排序字段
        /// </summary>
        public string sidx { get; set; }

        /// <summary>
        /// 升序或者降序
        /// </summary>
        public string sort { get; set; }
        /// <summary>
        /// 过滤条件
        /// </summary>
        public string strWhere { get; set; }

    }

}
